文章背景与核心概要

在现代数据库架构中，数据访问控制对于保障系统安全性至关重要。本文深入探讨了 PostgreSQL 强大的权限控制模型，该模型建立在“对象（Object）、权限（Privilege）和角色（Role）”这一核心框架之上。文章系统梳理了从基础概念、用户与组角色的创建，到对象权限授予、默认访问权限配置以及特殊角色的完整工作流。

通过阅读本文，开发者和数据库管理员（DBA）可以全面掌握如何通过角色继承、细粒度权限分配以及默认特权（Default Privileges）来简化权限管理，同时避免过度授权带来的安全隐患，在保障数据库环境安全的同时降低运维摩擦。

Postgres Roles and Privileges

Summary

Controlling access to data in PostgreSQL is paramount for security. This comprehensive guide covers the fundamentals of PostgreSQL’s permissions model, which is built on the object, privilege, and role framework.

Key takeaways include: * Every database object has an owner who exercises complete control and can grant privileges. * Privileges dictate what operations (such as SELECT, INSERT, or EXECUTE) can be performed on specific database objects. * Roles act as users (can log in) or groups (cannot log in), simplifying permission management. * Users can inherit permissions from groups they belong to. * The special public role is implicitly inherited by all users, granting default privileges (like connection and basic usage) that can be explicitly revoked. * Superusers bypass all privilege checks and should be used sparingly for security reasons. * Default privileges allow administrators to automatically assign permissions to future objects rather than applying them manually each time.

控制 PostgreSQL 中的数据访问对于系统安全至关重要。本综合指南涵盖了 PostgreSQL 权限模型的基础知识，该模型构建于对象（object）、权限（privilege）和角色（role）框架之上。

核心要点包括： * 每个数据库对象都有一个所有者（owner），该所有者拥有完全的控制权并可以授予权限。 * 权限（Privileges）决定了可以在特定数据库对象上执行哪些操作（例如 SELECT、INSERT 或 EXECUTE）。 * 角色（Roles）充当用户（可以登录）或组（不能登录），从而简化了权限管理。 * 用户可以继承其所属组的权限。 * 特殊的 public 角色隐式地被所有用户继承，它授予默认权限（如连接和基本使用权），这些权限可以显式撤销。 * 超级用户（Superusers）绕过所有权限检查，出于安全原因应谨慎使用。 * 默认权限（Default privileges）允许管理员自动为未来的对象分配权限，而不必每次都手动应用。

1. Basic Concepts

To understand PostgreSQL's access control model, it helps to break down its core terminology:

Database Object: Any entity created in the database, such as tables, views, types, functions, and triggers.
Privilege: A rule controlling what operations a role can perform on an object (e.g., SELECT on a table, EXECUTE on a function).
Role: An overarching term for a database user or group.
Owner: The role that created an object. Owners have total control, including the ability to grant privileges or transfer ownership.

1. 基本概念

为了理解 PostgreSQL 的访问控制模型，我们首先需要拆解其核心术语：

数据库对象（Database Object）： 在数据库中创建的任何实体，例如表、视图、类型、函数和触发器。

权限（Privilege）： 控制角色可以在对象上执行哪些操作的规则（例如，对表执行 SELECT，对函数执行 EXECUTE）。

角色（Role）： 数据库用户或组的统称。

所有者（Owner）： 创建对象的角色。所有者拥有完全的控制权，包括授予权限或转移所有权的权力。

2. Setting Up

Connect to your database using the psql command-line tool via your connection string:

➜ psql postgres://[USER].[YOUR-PROJECT-REF]:[YOUR-PASSWORD]@[REGION-SUBDOMAIN].pooler.supabase.com:5432/postgres

Verify your active role:

postgres=> select current_role;
┌──────────────┐
│ current_role │
├──────────────┤
│ postgres     │
└──────────────┘
(1 row)

2. 环境准备

使用连接字符串通过 psql 命令行工具连接到您的数据库：

➜ psql postgres://[USER].[YOUR-PROJECT-REF]:[YOUR-PASSWORD]@[REGION-SUBDOMAIN].pooler.supabase.com:5432/postgres

验证您当前处于活动状态的角色：

postgres=> select current_role;
┌──────────────┐
│ current_role │
├──────────────┤
│ postgres     │
└──────────────┘
(1 row)

3. Creating Roles

Create two users, junior_dev and senior_dev, using the LOGIN attribute:

postgres=> create role junior_dev login password 'a long and secure password';
CREATE ROLE
postgres=> create role senior_dev login password 'another long and secure password';
CREATE ROLE

3. 创建角色

使用 LOGIN 属性创建两个用户 junior_dev 和 senior_dev：

postgres=> create role junior_dev login password 'a long and secure password';
CREATE ROLE
postgres=> create role senior_dev login password 'another long and secure password';
CREATE ROLE

4. Creating Objects and Assigning Privileges

When junior_dev attempts to create a table in the public schema without prior authorization, PostgreSQL will deny access:

postgres=> create table public.apps(id serial primary key, name text);
ERROR:  permission denied for schema public

Granting Schema and Table Privileges

Switch to the postgres user and grant the CREATE privilege on the public schema:

postgres=> grant create on schema public to junior_dev;
GRANT

Once junior_dev creates the table, they become its owner. To allow senior_dev to query the table, the owner must grant the appropriate permissions:

# As junior_dev
postgres=> grant select on public.apps to senior_dev;
GRANT

Alternatively, you can grant privileges WITH GRANT OPTION so another user can delegate access on your behalf:

postgres=> grant select on public.apps to postgres with grant option;
GRANT

4. 创建对象与分配权限

当 junior_dev 在未经事先授权的情况下尝试在 public 模式（schema）中创建表时，PostgreSQL 将拒绝访问：
postgres=> create table public.apps(id serial primary key, name text);
ERROR:  permission denied for schema public
授予模式和表权限

切换到 postgres 用户并授予 public 模式的 CREATE 权限：
postgres=> grant create on schema public to junior_dev;
GRANT
一旦 junior_dev 创建了该表，他便成为了该表的所有者（owner）。为了允许 senior_dev 查询该表，所有者必须授予相应的权限：
# As junior_dev
postgres=> grant select on public.apps to senior_dev;
GRANT
或者，您可以使用 WITH GRANT OPTION 授予权限，以便其他用户可以代表您委派访问权限：
postgres=> grant select on public.apps to postgres with grant option;
GRANT

5. Default Access Privileges

To avoid manually granting privileges every time a new table is created, you can update a role's default access privileges:

postgres=> alter default privileges in schema public grant select on tables to senior_dev;
ALTER DEFAULT PRIVILEGES

Now, any future tables created by junior_dev will automatically grant SELECT privileges to senior_dev.

5. 默认访问权限

为了避免每次创建新表时都手动授予权限，您可以更新角色的默认访问权限（default access privileges）：
postgres=> alter default privileges in schema public grant select on tables to senior_dev;
ALTER DEFAULT PRIVILEGES
现在，以后由 junior_dev 创建的任何新表都将自动向 senior_dev 授予 SELECT 权限。

6. Creating Groups

Rather than assigning object privileges individually, you can use a group role (created with NOLOGIN) and add users as members:

-- Create a non-login group role
postgres=> create role developers nologin;
CREATE ROLE

-- Grant schema creation to the group
postgres=> grant create on schema public to developers;

-- Add developers to the group
postgres=> grant developers to junior_dev;
postgres=> grant developers to senior_dev;

Users can then temporarily assume the group identity to create shared resources:

# As junior_dev
postgres=> set role developers;
postgres=> create table public.apps(id serial primary key, name text);
postgres=> reset role;

6. 创建组

与其单独分配对象权限，不如使用组角色（通过 NOLOGIN 创建）并将用户添加为成员：

-- 创建一个不可登录的组角色
postgres=> create role developers nologin;
CREATE ROLE

-- 授予该组模式创建权限
postgres=> grant create on schema public to developers;

-- 将开发人员添加到组中
postgres=> grant developers to junior_dev;
postgres=> grant developers to senior_dev;

然后，用户可以临时承担组身份来创建共享资源：

# As junior_dev
postgres=> set role developers;
postgres=> create table public.apps(id serial primary key, name text);
postgres=> reset role;

7. Role Attributes

Roles can be configured with specific attributes to manage their database capabilities:

login — Controls login permissions.
superuser — Bypasses all privilege checks.
createdb — Allows creating databases.
createrole — Allows creating other roles.
replication — Permits initiating replication.
bypassrls — Bypasses row-level security.
connection limit — Restricts concurrent connections.
inherit — Inherits permissions from member groups.

7. 角色属性

可以为角色配置特定的属性来管理其数据库功能：

login — 控制登录权限。

superuser — 绕过所有权限检查。

createdb — 允许创建数据库。

createrole — 允许创建其他角色。

replication — 允许初始化复制。

bypassrls — 绕过行级安全性（RLS）。

connection limit — 限制并发连接数。

inherit — 从成员组继承权限。

8. Special Roles

Superuser: The ultimate administrative role. It bypasses all privilege checks except authentication. Use with extreme caution.
Public: A built-in pseudo-role that every user implicitly belongs to. It handles default application permissions like database connectivity and function execution. Privileges granted to public apply to all users globally.

8. 特殊角色

超级用户（Superuser）： 终极管理角色。它会绕过除身份验证之外的所有权限检查。请极其谨慎地使用。

Public： 一个内置的伪角色，每个用户都隐式属于该角色。它处理默认的应用程序权限，例如数据库连接和函数执行授予。授予 public 的权限全局适用于所有用户。

9. Summary Checklist

Every database object has an owner.
Object operations are governed by privileges.
Owners can delegate privileges via the GRANT command.
Groups streamline permission structures by letting users inherit rights collectively.
The PUBLIC role affects all users implicitly.
Default privileges automate permissions for newly created objects.

9. 总结清单

每个数据库对象都有一个所有者。

对象操作由权限控制。

所有者可以通过 GRANT 命令委派权限。

组允许用户集体继承权利，从而简化了权限结构。

PUBLIC 角色隐式影响所有用户。

默认权限可自动为新创建的对象配置权限。

10. Conclusion

PostgreSQL features a powerful, fine-grained access control architecture. Understanding the nuances of ownership, group inheritance, default privileges, and special roles allows database administrators to secure environments effectively while minimizing operational friction.

10. 结论

PostgreSQL 具有强大且细粒度的访问控制架构。理解所有权、组继承、默认权限和特殊角色的细微差别，使数据库管理员能够有效地保护环境，同时将运维摩擦降至最低。